package com.example.tool.db;

import com.example.tool.entity.ConfigDBEntity;
import com.example.tool.module.ModuleInformation;
import com.example.tool.utils.ActionToolUtil;
import com.intellij.openapi.project.Project;
import oracle.sql.CLOB;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.lang.reflect.Constructor;
import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class SqlExecute {
  private static final Logger LOGGER = LoggerFactory.getLogger(SqlExecute.class);

  /**
   * 获取连接对象
   * @param project 项目文件
   */
  private static Connection getConnection(Project project) throws Exception {
    ConfigDBEntity configDBEntity = ActionToolUtil.getInformationDB(project);
    if (configDBEntity == null) {
      throw new Exception(ModuleInformation.NO_CONFIG_DB);
    }
    String dbType = configDBEntity.getDbType();
    String dbUrl = configDBEntity.getDbUrl().trim();
    String dbUser = configDBEntity.getDbUser().trim();
    String dbPassword = configDBEntity.getDbPassword().trim();
    return SqlConnection.getInstance().getConnection(dbType, dbUrl, dbUser, dbPassword);
  }

  /**
   * 查询U8C提供的数据类型
   * @param project 项目对象
   * @param clazz   实体类
   * @param sqlExec 要执行的sql语句集合
   * @return 对应的实体类集合
   */
  public static <T> List<T> query(Project project, Class<T> clazz, SqlExec sqlExec) throws Exception {
    ConfigDBEntity configDBEntity = ActionToolUtil.getInformationDB(project);
    if (configDBEntity == null) {
      throw new Exception(ModuleInformation.NO_CONFIG_DB);
    }
    String dbType = configDBEntity.getDbType();
    String sql = sqlExec.getSql(dbType);
    return query(project, clazz, sql);
  }

  /**
   * 查询U8C提供的数据类型
   * @param project 项目对象
   * @param clazz   实体类
   * @param sql     要执行的sql语句
   * @return 对应的实体类集合
   */
  public static <T> List<T> query(Project project, Class<T> clazz, String sql) throws Exception {
    Connection connection = getConnection(project);
    PreparedStatement preparedStatement = null;
    ResultSet resultSet = null;
    try {
      String databaseProductName = connection.getMetaData().getDatabaseProductName();
      // Oracle数据库需要去除末尾的分号
      if (databaseProductName.equals("Oracle")) {
        String trim = sql.trim();
        if (trim.endsWith(";")) {
          sql = trim.substring(0, trim.length() - 1);
        }
      }
      preparedStatement = connection.prepareStatement(sql);
      resultSet = preparedStatement.executeQuery();
      return handleClass(clazz, resultSet);
    } catch (Exception e) {
      LOGGER.info("执行sql语句：{}", sql);
      LOGGER.info("查询数据出错：{}", e.getMessage(), e);
      throw new Exception("查询数据出错！" + e.getMessage());
    } finally {
      closeStream(connection, resultSet, preparedStatement);
    }
  }


  /**
   * 关闭连接 按顺序关闭
   * @param connection     数据库连接对象
   * @param autoCloseables 可管理的流对象
   */
  private static void closeStream(Connection connection, AutoCloseable... autoCloseables) {
    try {
      for (AutoCloseable autoCloseable : autoCloseables) {
        if (autoCloseable != null) {
          autoCloseable.close();
        }
      }
      if (connection != null) {
        connection.close();
      }
    } catch (Exception e) {
      LOGGER.info("关闭数据库连接发生错误：{}", e.getMessage(), e);
    }
  }

  /**
   * 将查询的数据封装成实体类
   * @param clazz     类
   * @param resultSet 查询的数据集合
   * @return 类的集合
   */

  @SuppressWarnings("unchecked")
  private static <T> List<T> handleClass(Class<T> clazz, ResultSet resultSet) throws Exception {
    ArrayList<T> tArrayList = new ArrayList<>();
    while (resultSet.next()) {
      Constructor<T> constructor = clazz.getConstructor();
      T t = constructor.newInstance();
      // 用来判断是不是原始类型
      if (clazz.isPrimitive()) {
        tArrayList.add((T) resultSet.getObject(1));
      } else if (clazz.getName().equals("java.lang.String")) {
        tArrayList.add((T) resultSet.getObject(1));
      } else {
        for (Field field : clazz.getDeclaredFields()) {
          field.setAccessible(true);
          try {
            int index = resultSet.findColumn(field.getName());
            if (index == 0) continue;
            Object object = resultSet.getObject(index);
            if (field.getType().getSimpleName().equals("String")) {// 实体类字段类型是String 不管查询的是什么类型的都转为String
              if (object == null) {
                field.set(t, "");
              } else {
                if (object instanceof CLOB clob) {// Oracle 数据库类型
                  String clobSubString = clob.getSubString(1, (int) clob.length());
                  field.set(t, clobSubString);
                } else {
                  field.set(t, String.valueOf(object));
                }
              }
            } else {
              field.set(t, object);
            }
          } catch (Exception e) {
            // 静默处理
          }
        }
        tArrayList.add(t);
      }
    }
    return tArrayList;
  }
}
